﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace WL
{
    class ExcelMM
    {
        public static List<string[]> getDatas(string filename, string sheetName)
        {
            List<string[]> list = new List<string[]>();

            IWorkbook hssfworkbook = null;
            try
            {
                hssfworkbook = new HSSFWorkbook(new FileStream(filename, FileMode.Open));
            }
            catch (Exception)
            {
                try
                {
                    hssfworkbook = new XSSFWorkbook(new FileStream(filename, FileMode.Open));
                }
                catch (Exception e)
                {
                    System.Windows.Forms.MessageBox.Show(e.Message);
                }
            }
            
            ISheet sheet = hssfworkbook.GetSheet(sheetName);

            int index = 0;

            string s = "";
            while (index<=10)
            {
                
                

                // 判断该行是否存在打卡记录
                IRow row = sheet.GetRow(index);
                if (row == null) break;

                string[] arr = getArrayByRow(row);
                
                //for (int i = 0; i < arr.Length; i++)
                //{
                //    s += "," + arr[i];
                //    //System.Windows.Forms.MessageBox.Show(arr[i].ToString());
                //}
                //s += "\n";
                //dt.Rows.Add(dr);
                //System.Windows.Forms.MessageBox.Show(arr.Length.ToString());
                //object s = ExcelMM.getDataByCell( row.GetCell(1));//.StringCellValue; // 从xls读取打卡职工empid
                //s += "|" + ExcelMM.getDataByCell(row.GetCell(2));
                list.Add(arr);

                //System.Windows.Forms.MessageBox.Show(s.ToString());
                index++;
            //MessageBox.Show("导入成功");
            }
            System.Windows.Forms.MessageBox.Show(s);

            
            

            return list;
        }

        /// <summary>
        /// 判断单元格是否存在数据
        /// </summary>
        /// <returns></returns>
        private static bool existData(ICell cell)
        {
            if (cell != null)
            {
                if (!cell.ToString().Equals(""))
                {
                    return true;
                }
            }

            return false;
        }

        /// <summary>
        /// 根据文件和sheetName获取ISheet对象
        /// </summary>
        /// <param name="filename">文件名</param>
        /// <param name="sheetName">sheetName</param>
        /// <returns>ISheet对象</returns>
        private static ISheet getSheet(string filename, string sheetName)
        {
            IWorkbook hssfworkbook = null;
            try
            {
                hssfworkbook = new HSSFWorkbook(new FileStream(filename, FileMode.Open));
            }
            catch (Exception)
            {
                try
                {
                    hssfworkbook = new XSSFWorkbook(new FileStream(filename, FileMode.Open));
                }
                catch (Exception e)
                {
                    System.Windows.Forms.MessageBox.Show(e.Message);
                }
            }

            ISheet sheet = hssfworkbook.GetSheet(sheetName);
            hssfworkbook = null;
            return sheet;
        }

        private static string[] getArrayByRow(IRow row)
        {
            List<string> list = new List<string>();
            
            int index = 0;
            while (existData(row.GetCell(index)))
            {
                list.Add(row.GetCell(index).ToString());
                index++;
            }

            return list.ToArray();
        }

        public static object getDataByCell(ICell cell)
        {

            return cell.ToString();

        }
        public static DataTable getDataTableByExcel(string filename, string sheetName)
        {
            ISheet sheet1 = getSheet(filename, sheetName); // get sheet
            DataTable table = new DataTable();//  
            var row1 = sheet1.GetRow(0);//获取第一行即标头  
            int cellCount = row1.LastCellNum; //第一行的列数  
            //把第一行的数据添加到datatable的列名  
            for (int i = row1.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(row1.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            table.Columns.Add(new DataColumn("line")); //存放行数的列

            int rowCount = sheet1.LastRowNum; //总行数


            //把每行数据添加到datatable中  
            for (int i = (sheet1.FirstRowNum + 1); i <= sheet1.LastRowNum; i++)
            {
                IRow row = sheet1.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    try
                    {
                        if (row.GetCell(j) != null)
                        {
                            if (CellType.Numeric == row.GetCell(j).CellType)
                            {
                                dataRow[j] = row.GetCell(j).NumericCellValue;
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }                       
                    }catch (Exception e)
                    { 
                    }
                }
                dataRow[table.Columns.Count - 1] = i + 1; //excel行数，默认第一列是字段

                table.Rows.Add(dataRow);
            }
            //到这里 table 已经可以用来做数据源使用了
            sheet1 = null;  //清空sheet

            return table;
        }
        internal static DataTable getData(string filename, string sheetName)
        {

            ISheet sheet1 = getSheet(filename, sheetName); // get sheet
            DataTable table = new DataTable();//  
            var row1 = sheet1.GetRow(0);//获取第一行即标头  
            int cellCount = row1.LastCellNum; //第一行的列数  
            //把第一行的数据添加到datatable的列名  
            for (int i = row1.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(row1.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            table.Columns.Add(new DataColumn("line")); //存放行数的列

            int rowCount = sheet1.LastRowNum; //总行数
            

            //把每行数据添加到datatable中  
            for (int i = (sheet1.FirstRowNum + 1); i <= sheet1.LastRowNum; i++)
            {
                IRow row = sheet1.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dataRow[table.Columns.Count - 1] = i + 1; //excel行数，默认第一列是字段

                table.Rows.Add(dataRow);
            }
            //到这里 table 已经可以用来做数据源使用了
            sheet1 = null;  //清空sheet</pre><br>  

            return table;
        }
    }
}
